
package br.unipampa.dados;

import br.unipampa.view.ApoioDeInterface;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author Gabriel Moro
 * @since 15/03/2013
 * @version 1.2
 */
public class DBUsuario extends DML {

    private String campos;
    private Object[] valores;

    public DBUsuario(Connection con) {
        super.connection = (com.mysql.jdbc.Connection) con;
        super.table = "usuarios";
        this.campos = "(nome_usuarios,senha_usuarios,matricula_usuarios"
                + ",universidade_usuarios,endUniversidade_usuarios,login_usuarios,codigoTipoDeUser)";
        this.valores = new Object[7];
    }

    @Override
    public ArrayList<Object> select(String coluna) {
        try {
            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
            super.resultSet= super.resultSet = super.statement.executeQuery("SELECT " + coluna + " FROM " + super.table + " ORDER BY nome_usuarios ASC;");
          int cont=0;
          ArrayList<Object> valores= new ArrayList<>();
            while(super.resultSet.next()){
               valores.add(super.resultSet.getString(1));
               valores.add(super.resultSet.getString(2));
               valores.add(definiValorTipoUSer(super.resultSet.getInt(3)));
           }
            return valores;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return null;
    }
   public ArrayList<Object> selectUser(String login,String senha) {
       try {
           ArrayList<Object> dados= new ArrayList<>();
            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
           super.resultSet=  super.resultSet = super.statement.executeQuery("SELECT login_usuarios, senha_usuarios, codigoTipoDeUser,nome_usuarios FROM "+ super.table+" WHERE login_usuarios LIKE '%"+login+"%' and "
                   + "senha_usuarios= '"+ senha+"';");
           
           while(super.resultSet.next()){
              dados.add(super.resultSet.getString(1));
              dados.add(super.resultSet.getString(2));
              dados.add(super.resultSet.getInt(3));
              dados.add(super.resultSet.getString(4));
           }
           return dados;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
       return null;
   }
   
    public ArrayList<Object> selectEdicao(String nome) {
       try {
           ArrayList<Object> dados= new ArrayList<>();
            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
            super.resultSet=  super.resultSet = super.statement.executeQuery("SELECT matricula_usuarios,universidade_usuarios,endUniversidade_usuarios,login_usuarios FROM "+ super.table+" WHERE nome_usuarios = '"+nome+"';");
     
           while(super.resultSet.next()){
              dados.add(super.resultSet.getString(1));
              dados.add(super.resultSet.getString(2));
              dados.add(super.resultSet.getString(3));
              dados.add(super.resultSet.getString(4));
           }
           return dados;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
       return null;
   }
   
    @Override
    public int insert(String valores) {
        try {
            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
            int registrosAfetados = super.statement.executeUpdate("INSERT INTO " + super.table + " " + this.campos + " VALUES (" + valores + ");");
            return registrosAfetados;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return 0;
    }

    @Override
    public int delete(String valor) {
        try {
            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
            int registrosAfetados = super.statement.executeUpdate("DELETE FROM " + super.table + " WHERE nome_usuarios= '" + valor + "';");
            return registrosAfetados;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return 0;
    }
    
    @Override
    public void closedConnection() {
        try {
            super.resultSet.close();
            super.statement.close();
            super.connection.close();
        } catch (SQLException erroSQl) {
            ApoioDeInterface.mensagemErroBanco(erroSQl.getMessage());
        }

    }

    @Override
    public int update(String atributoX, String valores) {
         try {
            super.statement = (com.mysql.jdbc.Statement) super.connection.createStatement();
            int registrosAfetados = super.statement.executeUpdate("UPDATE "+ super.table+" SET "+ valores+ " WHERE nome_usuarios= '"+atributoX+"';");
            return registrosAfetados;
        } catch (SQLException ex) {
            ApoioDeInterface.mensagemErroBanco(ex.getMessage());
        }
        return 0;
    }
    public String definiValorTipoUSer(int num){
        if(num==1){
            return "Administrador";
        }if(num==2){
            return "Usuário Comum";
        }
        return null;
    }
}
